package com.ttl.common.util;

import hibernate.util.HibernateUtil;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Properties;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import net.sf.jasperreports.engine.JasperRunManager;

import org.apache.struts.action.ActionServlet;
import org.hibernate.Query;
import org.hibernate.ScrollableResults;
import org.hibernate.Session;
import org.hibernate.SessionFactory;

import com.ttl.bc.ap.bean.BCAP;
import com.ttl.bc.ap.db.BCAPDAO;
import com.ttl.bc.ap.invoice.bo.BCAPInvoiceDAO;

import com.ttl.bc.ap.purchaseorder.db.BCPurchaseOrderDAO;
import com.ttl.bc.ap.stkrequest.BCStkRequestDAO;
import com.ttl.bc.ar.reqconfirm.db.BCReqConfirmDAO;
import com.ttl.bc.ic.finishgoods.db.BCFinishGoodsDAO;
import com.ttl.bc.ic.item.BCItemBean;
import com.ttl.bc.ic.item.BCItemDAO;

import com.ttl.bc.master.department.BCDepartmentBean;
import com.ttl.bc.master.department.BCDepartmentDAO;
import com.ttl.bc.master.project.BCProjectBean;
import com.ttl.bc.master.project.BCProjectDAO;
import com.ttl.bc.stock.request2.db.BCStkIssue2DAO;

public class CommonUtils {

	public static String getIssueType(int issueType) {
		String issueTypeName = null;
		switch(issueType) {
			case 1:	issueTypeName="ใช้ภายในหน่วยงาน";			break;
			case 2:	issueTypeName="ใช้ในการซ่อมบำรุง";			break;
			case 3:	issueTypeName="ใช้ในการผลิตผ้า PD";		break;
			case 4:	issueTypeName="ใช้ในการผลิตผ้า YD";		break;
			case 5:	issueTypeName="ใช้ในการผลิตผ้า Car Seat";	break;
			case 6:	issueTypeName="ใช้ในการผลิตสินค้าอื่น (Commission)";	break;
			case 7:	issueTypeName="ใช้ในการผลิตเส้นด้าย";		break;
			case 8:	issueTypeName="ใช้ในการผลิตสินค้าตัวอย่าง NP";	break;
			case 9:	issueTypeName="สำรองไว้ในหน่วยงาน";		break;
		}
				
		return issueTypeName;
	}
	
	@SuppressWarnings("unchecked")
	public static Boolean writeBCItemFileCode() {
		BCItemDAO dao = new BCItemDAO();
		List results = dao.findAll();
		
		System.out.println("results.size()= "+results.size());
		if (results.size()>0) {	

			try {
			    Writer output = null;			    
			    
			    StringBuffer itemBuffer = new StringBuffer("var codes = [");
			    for(int i=0; i<results.size(); i++) {
			    	BCItemBean item = (BCItemBean)results.get(i); 
			    	itemBuffer.append("\""+item.getCode() +"\",");			    				    				    
			    }
			    			    			    			    
			    String textItems = new String(itemBuffer);
			    textItems = textItems.substring(0, textItems.length()-1);
			    textItems += "];" ; 
			    			    
			    System.out.print("textItems = "+textItems);
			    
			    File file = new File("D:\\F\\www.vaannila.com\\ittl\\WebContent\\css\\autocomplete\\data\\localdata2.js");
			    output = new BufferedWriter(new FileWriter(file));
			    output.write(textItems);
			    output.close();
			    System.out.println("\nYour file has been written");    
			} catch (Exception e) {
				System.out.println("create local javascript writeBCItemFileCode error \n");
			}
			
			return true;
		}else {			
			return false;
		}
	}
	
	@SuppressWarnings("unchecked")
	public static Boolean writeBCItemFileCodeAndName() {
		BCItemDAO dao = new BCItemDAO();
		List results = dao.findAll();
		
		System.out.println("results.size()= "+results.size());
		if (results.size()>0) {	

			try {
			    Writer output = null;
			    
			    
			    StringBuffer itemBuffer = new StringBuffer("var itemsName = [");
			    for(int i=0; i<results.size(); i++) {
			    	BCItemBean item = (BCItemBean)results.get(i);
			    	System.out.println("i="+i);
			    	
			    	StringBuffer chkName = new StringBuffer(""+item.getName1());
			    	StringBuffer nameItemBuffer = new StringBuffer();			    	
			    	for(int j=0; j<chkName.length(); j++) {
			    		if(chkName.charAt(j)!= '"') {
			    			nameItemBuffer.append(chkName.charAt(j));
			    		}
			    	}
			    	
			    	String nameItemStr = new String(nameItemBuffer);
			    	
			    	itemBuffer.append("\n{ code: \""+item.getCode() +"\", name: \""+ 
			    			new String(nameItemStr.getBytes("TIS-620"),"ISO8859_1") + "\"},");
			    	
			    	String a = new String(nameItemStr.getBytes("TIS-620"),"ISO8859_1");
			    	System.out.println(" a ->"+a);			    	
			    }
			    			    			    			    
			    String textItems = new String(itemBuffer);
			    textItems = textItems.substring(0, textItems.length()-1);
			    textItems += "];" ; 
			    
			    
			    System.out.print("textItems = "+textItems);
			    
			    File file = new File("D:\\F\\www.vaannila.com\\ittl\\WebContent\\css\\autocomplete\\data\\localdata2Name.js");
			    output = new BufferedWriter(new FileWriter(file));
			    output.write(textItems);
			    output.close();
			    System.out.println("\nYour file has been written");    
			} catch (Exception e) {
				System.out.println("create local javascript error \n"+e);
			}
			
			return true;
		}else {			
			return false;
		}
	}
	
	@SuppressWarnings("unchecked")
	public static String getMaxDocNo(String preDocNo) {
		Date date = new Date();
		
		SimpleDateFormat simpleYear = new SimpleDateFormat("yyyy");
		String yearStr = simpleYear.format(date);
		
		if(Integer.valueOf(yearStr)<2500) {
			int yearStr2 = Integer.valueOf(yearStr)+543;
			yearStr = (""+yearStr2).substring(2);
		} else {
			yearStr = yearStr.substring(2);
		}
		
		SimpleDateFormat simpleMonth = new SimpleDateFormat("MM");
		String monthStr = simpleMonth.format(date);
		
		String docNoStr = preDocNo+"-"+yearStr+monthStr;
		
		String hql = "SELECT MAX(docNo) FROM BCStkIssue2 WHERE docNo like '"+docNoStr+"%' ";

		BCStkIssue2DAO dao = new BCStkIssue2DAO();
				
//		String hql = "SELECT MAX(docNo) FROM ITTL_DocNoBean WHERE docNo like '"+docNoStr+"%' ";
//		System.out.println(hql);
//		ITTL_DocNoDAO dao = new ITTL_DocNoDAO();
		
		List result = dao.find(hql);
		String docNo = null;
		
		String maxDocNo = null;
		if(result.get(0) != null) {
			
			docNo = result.get(0).toString();
			int temp = 1+Integer.valueOf(docNo.substring(docNo.length()-3,docNo.length()));			
			NumberFormat formatter = new DecimalFormat("000");
			docNo = formatter.format(temp);
			
			maxDocNo = docNoStr+docNo; 			
		}else {
			maxDocNo = docNoStr+"001";
		}
						
		return maxDocNo;
	}

	public static String getCurrentDate() {
		Date date = new Date();
		
		SimpleDateFormat simpleYear = new SimpleDateFormat("yyyy-MM-dd");
		String currentDate = simpleYear.format(date);
								
		return currentDate;
	}
	
	@SuppressWarnings("unchecked")
	public static boolean writeBCProjectFileCode() {
		BCProjectDAO dao = new BCProjectDAO();
		String hql = "FROM BCProjectBean ORDER BY code";
		List results = dao.find(hql);
		
		System.out.println("results.size()= "+results.size());
		if (results.size()>0) {	

			try {
			    Writer output = null;			    
			    
			    StringBuffer itemBuffer = new StringBuffer("var projects = [");
			    for(int i=0; i<results.size(); i++) {
			    	BCProjectBean item = (BCProjectBean)results.get(i); 
			    	itemBuffer.append("\n\""+item.getCode() +"\",");			    				    				    
			    }
			    			    			    			    
			    String textItems = new String(itemBuffer);
			    textItems = textItems.substring(0, textItems.length()-1);
			    textItems += "];" ; 
			    			    
			    System.out.print("textItems = "+textItems);
			    
			    File file = new File("D:\\F\\www.vaannila.com\\ittl\\WebContent\\css\\autocomplete\\data\\localdata31.js");
			    output = new BufferedWriter(new FileWriter(file));
			    output.write(textItems);
			    output.close();
			    System.out.println("\nYour file has been written");    
			} catch (Exception e) {				
				System.out.println("create local javascript writeBCProjectFileCode error \n"+e.toString());
				
			}
			
			return true;
		}else {			
			return false;
		}
		
	}

	@SuppressWarnings("unchecked")
	public static boolean writeBCDepartmentFileCode() {
		BCDepartmentDAO dao = new BCDepartmentDAO();
		String hql = "FROM BCDepartmentBean ORDER BY code";
		List results = dao.find(hql);
		
		System.out.println("results.size()= "+results.size());
		if (results.size()>0) {	

			try {
			    Writer output = null;			    
			    
			    StringBuffer itemBuffer = new StringBuffer("var departments = [");
			    for(int i=0; i<results.size(); i++) {
			    	BCDepartmentBean item = (BCDepartmentBean)results.get(i); 
			    	itemBuffer.append("\n\""+item.getCode() +"\",");			    				    				    
			    }
			    			    			    			    
			    String textItems = new String(itemBuffer);
			    textItems = textItems.substring(0, textItems.length()-1);
			    textItems += "];" ; 
			    			    
			    System.out.print("textItems = "+textItems);
			    
			    File file = new File("D:\\F\\www.vaannila.com\\ittl\\WebContent\\css\\autocomplete\\data\\localdataDepartment.js");
			    output = new BufferedWriter(new FileWriter(file));
			    output.write(textItems);
			    output.close();
			    System.out.println("\nYour file has been written");    
			} catch (Exception e) {				
				System.out.println("create local javascript writeBCDepartmentFileCode error \n"+e.toString());				
			}
			
			return true;
		}else {			
			return false;
		}				
	}
	
	@SuppressWarnings("unchecked")
	public static boolean writeBCDepartmentFileCodeName() {
		BCDepartmentDAO dao = new BCDepartmentDAO();
		String hql = "FROM BCDepartmentBean ORDER BY code";
		List results = dao.find(hql);
		
		System.out.println("results.size()= "+results.size());
		if (results.size()>0) {	

			try {
			    Writer output = null;			    
			    
			    StringBuffer itemBuffer = new StringBuffer("var departmentsName = [");
			    for(int i=0; i<results.size(); i++) {
			    	BCDepartmentBean item = (BCDepartmentBean)results.get(i); 
			    	
			    				    	 
			    	itemBuffer.append("\n{ code: \""+item.getCode() +"\", name: \""+ 
			    			new String(item.getName().getBytes("TIS-620"),"ISO8859_1") + "\"},");
			    	
			    	String a = new String(item.getName().getBytes("TIS-620"),"ISO8859_1");
			    	System.out.println("a ->"+a);			    	
			    }
			    			    			    			    
			    String textItems = new String(itemBuffer);
			    textItems = textItems.substring(0, textItems.length()-1);
			    textItems += "];" ; 
			    			    
			    System.out.print("textItems = "+textItems);
			    
			    File file = new File("D:\\F\\www.vaannila.com\\ittl\\WebContent\\css\\autocomplete\\data\\localdataDepartmentName.js");
			    output = new BufferedWriter(new FileWriter(file));
			    output.write(textItems);
			    output.close();
			    System.out.println("\nYour file has been written");    
			} catch (Exception e) {				
				System.out.println("create local javascript writeBCDepartmentFileCode error \n"+e.toString());				
			}			
			return true;
		}else {			
			return false;
		}				
	}	
	
	public static String getDateTimeForStamp() {
		
		DateFormat df = new SimpleDateFormat("dd/MM/yyyy hh:mm:ss a");
		
		return df.format(new Date());
	}
	
	public static String getDateForStamp() {
		Locale locale = new Locale("us", "US");
		
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd",locale);	
		return df.format(new Date());
	}	
	
	public static Date convertStringToDate(String dateStr) {
		
		Date date = new Date();
		try {    
//			System.out.println("before convert date: "+dateStr);
/*			String dateStr2[] = dateStr.split("-");
        	DateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
        	
        	String dateStr3 = dateStr2[2]+"/"+dateStr2[1]+"/"+(Integer.valueOf(dateStr2[0])+543);
        	date = formatter.parse(dateStr3); */   
			
			DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd",Locale.US);
        	date = formatter.parse(dateStr);   
        	//System.out.println("after convert date: "+date);
        	//System.out.println(new Date());
        	
	   } catch (Exception e) {
		   System.out.println("Exception :"+e);    
	   }
	   
		return date;
	}

	@SuppressWarnings("unchecked")
	public static String getMaxStkIssueDocNo(String stkIssue2DocNo, String departCode) {
		String tempDocNo = stkIssue2DocNo; 
		stkIssue2DocNo = stkIssue2DocNo.substring(0, 2).toUpperCase() ;				
		
		Date date = new Date();		
		SimpleDateFormat simpleYear = new SimpleDateFormat("yyyy");
		String yearStr = simpleYear.format(date);
		
		if(Integer.valueOf(yearStr)<2500) {
			int yearStr2 = Integer.valueOf(yearStr)+543;
			yearStr = (""+yearStr2).substring(2);
		} else {
			yearStr = yearStr.substring(2);
		}
		
		SimpleDateFormat simpleMonth = new SimpleDateFormat("MM");
		String monthStr = simpleMonth.format(date);
		
		String docNoStr="";
		String maxDocNo = null;
		if(stkIssue2DocNo.equals("F2") && departCode.equals("000")     ){
			docNoStr = "TO-T2-"+yearStr+monthStr;
		
			String hql = "SELECT MAX(docNo) FROM BCStkIssueBean WHERE docNo like '"+docNoStr+"%' ";
	
			BCStkIssue2DAO dao = new BCStkIssue2DAO();			
			
			List result = dao.find(hql);
			String docNo = null;
			
			
			if(result.get(0) != null) {			
				docNo = result.get(0).toString();
				int temp = 1+Integer.valueOf(docNo.substring(docNo.length()-4,docNo.length()));			
				NumberFormat formatter = new DecimalFormat("0000");
				docNo = formatter.format(temp);
				
				maxDocNo = docNoStr+"-"+docNo; 			
			}else {
				maxDocNo = docNoStr+"-0001";
			}
		}else {
			maxDocNo = tempDocNo;
		}		
		
		return maxDocNo;
	}

	@SuppressWarnings("unchecked")
	public static String getMaxStkRequestDocNo(String preDocNo) {
		Date date = new Date();
		
		SimpleDateFormat simpleYear = new SimpleDateFormat("yyyy");
		String yearStr = simpleYear.format(date);
		
		if(Integer.valueOf(yearStr)<2500) {
			int yearStr2 = Integer.valueOf(yearStr)+543;
			yearStr = (""+yearStr2).substring(2);
		} else {
			yearStr = yearStr.substring(2);
		}
		
		SimpleDateFormat simpleMonth = new SimpleDateFormat("MM");
		String monthStr = simpleMonth.format(date);
		
		String docNoStr = preDocNo+"-PR-"+yearStr+monthStr;
		
		String hql = "SELECT MAX(docNo) FROM BCStkRequestBean WHERE docNo like '"+docNoStr+"%' ";

		BCStkRequestDAO dao = new BCStkRequestDAO();				
		
		List result = dao.find(hql);
		String docNo = null;
		
		String maxDocNo = null;
		if(result.get(0) != null) {
			
			docNo = result.get(0).toString();
			int temp = 1+Integer.valueOf(docNo.substring(docNo.length()-3,docNo.length()));			
			NumberFormat formatter = new DecimalFormat("000");
			docNo = formatter.format(temp);
			
			maxDocNo = docNoStr+"-"+docNo; 			
		}else {
			maxDocNo = docNoStr+"-001";
		}
						
		return maxDocNo;
	}

	@SuppressWarnings("unchecked")
	public static String getMaxBCReqComfirmDocNo() {
		
		Date date = new Date();
		
		SimpleDateFormat simpleYear = new SimpleDateFormat("yyyy");
		String yearStr = simpleYear.format(date);
		
		if(Integer.valueOf(yearStr)<2500) {
			int yearStr2 = Integer.valueOf(yearStr)+543;
			yearStr = (""+yearStr2).substring(2);
		} else {
			yearStr = yearStr.substring(2);
		}
		
		SimpleDateFormat simpleMonth = new SimpleDateFormat("MM");
		String monthStr = simpleMonth.format(date);
		
		String docNoStr = "AP"+yearStr+monthStr;
		
		String hql = "SELECT MAX(docNo) FROM BCReqConfirmBean WHERE docNo like '"+docNoStr+"%' ";

		BCReqConfirmDAO dao = new BCReqConfirmDAO();				
		
		List result = dao.find(hql);
		String docNo = null;
		
		String maxDocNo = null;
		if(result.get(0) != null) {
			
			docNo = result.get(0).toString();
			int temp = 1+Integer.valueOf(docNo.substring(docNo.length()-4,docNo.length()));			
			NumberFormat formatter = new DecimalFormat("0000");
			docNo = formatter.format(temp);
			
			maxDocNo = docNoStr+"-"+docNo; 			
		}else {
			maxDocNo = docNoStr+"-0001";
		}
						
		return maxDocNo;
	}

	public static String getMaxBCPurchaseOrderDocNo(String preDocNo) {
		
		Date date = new Date();
		
		SimpleDateFormat simpleYear = new SimpleDateFormat("yyyy");
		String yearStr = simpleYear.format(date);
		
		if(Integer.valueOf(yearStr)<2500 && !preDocNo.contains("DC")) {
			int yearStr2 = Integer.valueOf(yearStr)+543;
			yearStr = (""+yearStr2).substring(2);
		} else {
			yearStr = yearStr.substring(2);
		}
		 
		SimpleDateFormat simpleMonth = new SimpleDateFormat("MM");
		String monthStr = simpleMonth.format(date);
		
		//String docNoStr = "PO1-"+yearStr+monthStr;
		String docNoStr = preDocNo+yearStr+monthStr;
		
		String hql = "SELECT MAX(docNo) FROM BCPurchaseOrderBean WHERE docNo like '"+docNoStr+"%' ";
		System.out.println(hql);
		BCPurchaseOrderDAO dao = new BCPurchaseOrderDAO();				
		
		List result = dao.find(hql);
		String docNo = null;
		
		String maxDocNo = null;
		if(result.get(0) != null) {
			
			docNo = result.get(0).toString();
			System.out.println(docNo);
			if(!preDocNo.contains("DC")) {
				int temp = 1+Integer.valueOf(docNo.substring(docNo.length()-4,docNo.length()));			
				NumberFormat formatter = new DecimalFormat("0000");
				docNo = formatter.format(temp);
			}else {
				int temp = 1+Integer.valueOf(docNo.substring(docNo.length()-3,docNo.length()));			
				NumberFormat formatter = new DecimalFormat("000");
				docNo = formatter.format(temp);				
			}
			maxDocNo = docNoStr+"-"+docNo; 			
		}else {
			if(!preDocNo.contains("DC")) {
				maxDocNo = docNoStr+"-0001";
			}else {
				maxDocNo = docNoStr+"-001";
			}
		}
						
		return maxDocNo;
	}

	@SuppressWarnings("unchecked")
	public static boolean writeBCAPFileCodeName() {

		BCAPDAO dao = new BCAPDAO();
		String hql = "FROM BCAP ORDER BY code";
		List results = dao.find(hql);
		
		System.out.println("results.size()= "+results.size());
		if (results.size()>0) {	

			try {
			    Writer output = null;			    
			    
			    StringBuffer apBuffer = new StringBuffer("var apCodeName = [");
			    for(int i=0; i<results.size(); i++) {
			    	BCAP ap = (BCAP)results.get(i); 
			    	
			    				    	 
			    	apBuffer.append("\n{ code: \""+ap.getCode() +"\", name: \""+ 
			    			new String(ap.getName1().getBytes("TIS-620"),"ISO8859_1") + "\"},");
			    	
			    	String a = new String(ap.getName1().getBytes("TIS-620"),"ISO8859_1");
			    	System.out.println("a ->"+a);			    	
			    }
			    			    			    			    
			    String textItems = new String(apBuffer);
			    textItems = textItems.substring(0, textItems.length()-1);
			    textItems += "];" ; 
			    			    
			    System.out.print("textItems = "+textItems);
			    
			    File file = new File("D:\\F\\www.vaannila.com\\ittl\\WebContent\\css\\autocomplete\\data\\apCodeName.js");
			    output = new BufferedWriter(new FileWriter(file));
			    output.write(textItems);
			    output.close();
			    System.out.println("\nYour file has been written");    
			} catch (Exception e) {				
				System.out.println("create local javascript writeBCAPFileCodeName error \n"+e.toString());				
			}			
			return true;
		}else {			
			return false;
		}		
		
	}

	@SuppressWarnings("unchecked")
	public static boolean writeBCAPFileCode() {
		
		BCAPDAO dao = new BCAPDAO();
		String hql = "FROM BCAP ORDER BY code";
		List results = dao.find(hql);
		
		System.out.println("results.size()= "+results.size());
		if (results.size()>0) {	

			try {
			    Writer output = null;			    
			    
			    StringBuffer itemBuffer = new StringBuffer("var apCode = [");
			    for(int i=0; i<results.size(); i++) {
			    	BCAP item = (BCAP)results.get(i); 
			    	itemBuffer.append("\n\""+item.getCode() +"\",");			    				    				    
			    }
			    			    			    			    
			    String textItems = new String(itemBuffer);
			    textItems = textItems.substring(0, textItems.length()-1);
			    textItems += "];" ; 
			    			    
			    System.out.print("textItems = "+textItems);
			    
			    File file = new File("D:\\F\\www.vaannila.com\\ittl\\WebContent\\css\\autocomplete\\data\\apCode.js");
			    output = new BufferedWriter(new FileWriter(file));
			    output.write(textItems);
			    output.close();
			    System.out.println("\nYour file has been written");    
			} catch (Exception e) {				
				System.out.println("create local javascript writeBCDepartmentFileCode error \n"+e.toString());				
			}
			
			return true;
		}else {			
			return false;
		}
		
	}

	public static String getCurrentDateFormat(String string) {
		Date date = new Date();
		SimpleDateFormat sdf = new SimpleDateFormat(string);			
		
		return sdf.format(date);
	}

	@SuppressWarnings("unchecked")
	public static String getBCPurchaseOrderDocNo(String docNo) {
		
		String hql = "FROM BCPurchaseOrderSubShowBean WHERE docNo like '"+docNo+"%' ";
		BCPurchaseOrderDAO dao = new BCPurchaseOrderDAO();						
		@SuppressWarnings("unused")
		List result = dao.find(hql);
		return null;
	}

	public static String convertDateToString(Date docDate) {
		
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		
		String date[] = sdf.format(docDate).split("-");
		
		if(Integer.valueOf(date[0])>2500) {
			date[0] = ""+ (Integer.valueOf(date[0])-543);
		}
		
		
		return date[0]+"-"+date[1]+"-"+date[2];
	}

	@SuppressWarnings("unchecked")
	public static Page getHibernatePage(String hibQuery, int pageNumber, int pageSize) {
		Page page = new Page();
		
		List result = null;
						
		long before = new Date().getTime();
		Session session = null;

		try {
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			
			session.beginTransaction();
			
			// ลำดับข้อมูลที่จะให้แสดงผล เช่น 0-100
			int offset = (pageNumber-1)*pageSize;
			
			Query q = session.createQuery(hibQuery);
						
			// จำนวนข้อมูลทั้งหมด
			page.setAllElement(q.list().size());
			page.setCurrentPage(pageNumber);
			page.setMaxEntriesPerPage(pageSize);
			
			result = q.setFirstResult(offset).setMaxResults(pageSize).list();						
			page.setResultList(result);
									
			session.getTransaction().commit();						
			
		}catch(Exception e) {			
			System.out.println(e.toString());
		}finally {
			session.close();
		}
		long after = new Date().getTime();
		double resultTime = (after-before)/1000d;
		System.out.println("\n\n\nresultTime: "+resultTime+" second");
		return page;
	}
	
	@SuppressWarnings("unchecked")
	public static List getSumQty(String hibQuery, int pageSize) {
		
		ArrayList result = new ArrayList();
		Session session = null;

		try {
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();			
			session.beginTransaction();
			
			Query q = session.createQuery(hibQuery);
		
			result = (ArrayList)q.list();
			
			session.getTransaction().commit();						
		}catch(Exception e) {			
			System.out.println(e.toString());
		}finally {
			session.close();
		}
		return result;
	}
	
//	@SuppressWarnings({ "rawtypes", "unchecked" })
//	public static List getScrollableResults(String hibQuery, int pageNumber, int pageSize) {
//		List results = new ArrayList();
//		
//		long before = new Date().getTime();
//		Session session = null;
//
//		try {
//			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
//			session = sessionFactory.openSession();
//			
//			session.beginTransaction();
//			
//			// ลำดับข้อมูลที่จะให้แสดงผล เช่น 0-100
//			int offset = (pageNumber-1)*pageSize;
//			
//			Query q = session.createQuery(hibQuery);
//						
//			ScrollableResults scr = q.scroll();
//			// ตรวจสอบก่อนว่าค้นหาเจอหรือไม่ (ถ้าเจอจะได้ ค่า RowNumber ที่ rec สุดท้ายมากกว่า -1
//			scr.last();
//			if(scr.getRowNumber()>-1) {
//				scr.first();			
//				scr.scroll(offset);
//				
//				int i=0;
//				while (pageSize>i++) {												
//					results.add(scr.get(0));				
//					if(!scr.next()) {
//						break;
//					}
//				}
//			}
//									
//			session.getTransaction().commit();						
//			
//		}catch(Exception e) {			
//			System.out.println(e.toString());
//		}finally {
//			session.close();
//		}
//		
//		long after = new Date().getTime();
//		double resultTime = (after-before)/1000d;
//		System.out.println("resultTime: "+resultTime+" second");		
//		return results;
//	}
	
	@SuppressWarnings("unchecked")
	public static Page getHibernateScrollableResultsPage(String hibQuery, int pageNumber, int pageSize) {
		Page page = new Page();
		
		ArrayList result = new ArrayList();
						
		long before = new Date().getTime();
		Session session = null;

		try {
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			session.beginTransaction();
			
			// ลำดับข้อมูลที่จะให้แสดงผล เช่น 0-100
			int offset = (pageNumber-1)*pageSize;
			
			Query q = session.createQuery(hibQuery);
						
			ScrollableResults scr = q.scroll();
			// ตรวจสอบก่อนว่าค้นหาเจอหรือไม่ (ถ้าเจอจะได้ ค่า RowNumber ที่ rec สุดท้ายมากกว่า -1
			scr.last();
			//System.out.println("scr.getRowNumber() = "+scr.getRowNumber());
			if(scr.getRowNumber()>-1) {
				scr.first();			
				scr.scroll(offset);
				
				int i=0;
				while (pageSize>i++) {												
					result.add(scr.get(0));				
					if(!scr.next()) {
						break;
					}
				}
	//			// จำนวนข้อมูลทั้งหมด
				scr.last();			
				page.setAllElement(scr.getRowNumber()+1);
				System.out.println("page.setAllElement(scr.getRowNumber()+1) = "+(scr.getRowNumber()+1));
				page.setCurrentPage(pageNumber);			
				page.setMaxEntriesPerPage(pageSize);				
				page.setResultList(result);
			}
			session.getTransaction().commit();						
			
		}catch(Exception e) {			
			System.out.println(e.toString());
		}finally {
			session.close();
		}
		
		long after = new Date().getTime();
		double resultTime = (after-before)/1000d;
		System.out.println("resultTime: "+resultTime+" second");

		return page;
	}

	public static boolean isValidDate(String inDate) {
						
		if (inDate == null)
			return false;

		//set the format to use as a constructor argument
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
		    
//		if (inDate.trim().length() != dateFormat.toPattern().length()) {			
//			return false;
//		}
		
		dateFormat.setLenient(false);
		    
		try {
		    //parse the inDate parameter
			dateFormat.parse(inDate.trim());
		}catch (ParseException pe) {
			return false;
		}
		
		
		return true;
	}

	public static boolean isNumber(String strNumber) {
		
		try {
			new java.math.BigDecimal(strNumber);
			return true;
		} catch (Exception e) {
			return false;
		}				
	}

	@SuppressWarnings("unchecked")
	public static String getMaxBCAPInvoiceDocNo(String docNo) {
		
		String docNoSub = docNo.substring(0, 3);
		
		Date date = new Date();
		
		SimpleDateFormat simpleYear = new SimpleDateFormat("yyyy");
		String yearStr = simpleYear.format(date);
		
		if(Integer.valueOf(yearStr)<2500) {
			int yearStr2 = Integer.valueOf(yearStr)+543;
			yearStr = (""+yearStr2).substring(2);
		} else {
			yearStr = yearStr.substring(2);
		}
		
		SimpleDateFormat simpleMonth = new SimpleDateFormat("MM");
		String monthStr = simpleMonth.format(date);
		
		String docNoStr = docNoSub+"-"+yearStr+monthStr;
		
		String hql = "SELECT MAX(docNo) FROM BCAPInvoiceBean WHERE docNo like '"+docNoStr+"%' ";

		BCAPInvoiceDAO dao = new BCAPInvoiceDAO();				
		
		List result = dao.find(hql);
		String docNo1 = null;
		
		String maxDocNo = null;
		if(result.get(0) != null) {
			
			docNo1 = result.get(0).toString();
			int temp = 1+Integer.valueOf(docNo1.substring(docNo1.length()-4,docNo1.length()));			
			NumberFormat formatter = new DecimalFormat("0000");
			docNo1 = formatter.format(temp);
			
			maxDocNo = docNoStr+"-"+docNo1; 			
		}else {
			maxDocNo = docNoStr+"-0001";
		}
						
		return maxDocNo;
	}

	public static boolean isNumberPercent(String strNumber) {
		try {
			
			if(strNumber.length()>0 && strNumber.charAt(strNumber.length()-1)=='%') {
				new java.math.BigDecimal(strNumber.substring(0, strNumber.length()-1));				
				return true;				
			}else {
				return false;
			}
		} catch (Exception e) {
			System.out.println(e.toString()+"   strNumber="+strNumber);
			return false;
		}
	}

	@SuppressWarnings("unchecked")
	public static String getMaxFinishGoodsDocNo(String preDocNoFG) {				
		
		Date date = new Date();		
		SimpleDateFormat simpleYear = new SimpleDateFormat("yyyy");
		String yearStr = simpleYear.format(date);
		
		if(Integer.valueOf(yearStr)<2500) {
			int yearStr2 = Integer.valueOf(yearStr)+543;
			yearStr = (""+yearStr2).substring(2);
		} else {
			yearStr = yearStr.substring(2);
		}
		
		SimpleDateFormat simpleMonth = new SimpleDateFormat("MM");
		String monthStr = simpleMonth.format(date);
		
		String docNoStr="";
		String maxDocNo = null;
		
		docNoStr = preDocNoFG+yearStr+monthStr;
	
		String hql = "SELECT MAX(docNo) FROM BCFinishGoodsBean WHERE docNo like '"+docNoStr+"%' ";

		BCFinishGoodsDAO dao = new BCFinishGoodsDAO();			
		
		List result = dao.find(hql);
		String docNo = null;
				
		if(result.get(0) != null) {			
			docNo = result.get(0).toString();
			int temp = 1+Integer.valueOf(docNo.substring(docNo.length()-4,docNo.length()));			
			NumberFormat formatter = new DecimalFormat("0000");
			docNo = formatter.format(temp);
			
			maxDocNo = docNoStr+"-"+docNo; 			
		}else {
			maxDocNo = docNoStr+"-0001";
		}
						
		return maxDocNo;
	}
	
	
	@SuppressWarnings("unchecked")
	public static void generateReport(String jasperFile, HashMap hashMap,
			HttpServletResponse response, ActionServlet servlet) throws Exception {
		
		
		if(CommonUtils.runReportToPdf(response, servlet, hashMap, jasperFile)==true) {
			System.out.println("run report to pdf: complete");
		}else {
			System.out.println("run Report to pdf: can't run report");
		}		
//		Connection connection;
//		ServletOutputStream servletOutputStream = response.getOutputStream();
//		
//		InputStream reportStream = servlet.getServletConfig()
//						.getServletContext().getResourceAsStream(jasperFile);
//		
//		response.setContentType("application/pdf");
//		
//		Class.forName("net.sourceforge.jtds.jdbc.Driver");		
//		connection = DriverManager.getConnection("jdbc:jtds:sqlserver://192.168.1.2:1433/ttl51;user=sa;password=admin@123");
//		
//		JasperRunManager.runReportToPdfStream(reportStream, servletOutputStream, hashMap, connection);
//		connection.close();
//		servletOutputStream.flush();
//		servletOutputStream.close();				
	}
	
	  public static final String DATE_FORMAT_NOW = "yyyy-MM-dd HH:mm:ss";

	  public static String now() {
	    Calendar cal = Calendar.getInstance();
	    SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT_NOW);
	    return sdf.format(cal.getTime());

	  }

	@SuppressWarnings("unchecked")
	public static boolean runReportToPdf(HttpServletResponse response,
			ActionServlet servlet, HashMap hashMap, String nameOfReport) {
		
		boolean runComplete = false;
		try {
			Connection connection;
			ServletOutputStream servletOutputStream = response.getOutputStream();
			
			InputStream reportStream = servlet.getServletConfig()
								.getServletContext().getResourceAsStream(nameOfReport);
			//@@@@@@@@@@
			InputStream propStream = servlet.getServletConfig()
						.getServletContext().getResourceAsStream("/config/config.properties");		
			Properties properties = new Properties();  
			properties.load(propStream);
			String database_server = properties.getProperty("database_server");
			String database_name = properties.getProperty("database_name");
			String port = properties.getProperty("port");
			String user = properties.getProperty("user");
			String password = properties.getProperty("password");
			//Constant.actionServlet = getServlet(); 
			//@@@@@@@@@@
			
			response.setContentType("application/pdf");		
			Class.forName("net.sourceforge.jtds.jdbc.Driver");
			connection = DriverManager.getConnection("jdbc:jtds:sqlserver://"+database_server+":"+port+"/"+database_name+";user="+user+";password="+password);
			//System.out.println("con ok");
			JasperRunManager.runReportToPdfStream(reportStream, servletOutputStream, hashMap, connection);
			//System.out.println("jasper ok");
			connection.close();
			servletOutputStream.flush();
			servletOutputStream.close();
			runComplete = true;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			System.out.println(e.getStackTrace());
		}
		return runComplete;
	}

	@SuppressWarnings("unchecked")
	public static String getMaxDocNoBCStkTransfer2() {
		Date date = new Date();
		
		SimpleDateFormat simpleYear = new SimpleDateFormat("yyyy");
		String yearStr = simpleYear.format(date);
		
		if(Integer.valueOf(yearStr)<2500) {
			int yearStr2 = Integer.valueOf(yearStr)+543;
			yearStr = (""+yearStr2).substring(2);
		} else {
			yearStr = yearStr.substring(2);
		}
		
		SimpleDateFormat simpleMonth = new SimpleDateFormat("MM");
		String monthStr = simpleMonth.format(date);
		
		String docNoStr = "F1-IF"+yearStr+monthStr;
		
		String hql = "SELECT MAX(docNo) FROM BCStkTransfer2 WHERE docNo like '"+docNoStr+"%' ";

		BCPurchaseOrderDAO dao = new BCPurchaseOrderDAO();				
		
		List result = dao.find(hql);
		String docNo = null;
		
		String maxDocNo = null;
		if(result.get(0) != null) {
			
			docNo = result.get(0).toString();
			int temp = 1+Integer.valueOf(docNo.substring(docNo.length()-4,docNo.length()));			
			NumberFormat formatter = new DecimalFormat("0000");
			docNo = formatter.format(temp);
			
			maxDocNo = docNoStr+"-"+docNo; 			
		}else {
			maxDocNo = docNoStr+"-0001";
		}
						
		return maxDocNo;
	}

	public static String getItemFind(HttpServletRequest request) throws Exception {
		String itemFind ="";
		if(request.getParameter("itemFind")!=null) {
			itemFind = ((String)request.getParameter("itemFind"));
			itemFind = new String(itemFind.getBytes("ISO8859_1"),"UTF-8");
			request.setAttribute("itemFind", itemFind.trim());
		}		
		return itemFind;
	}

	public static String getConvertStringToUTF8(String str) {
		String itemFind="";
		try {
			itemFind = new String(str.getBytes("ISO8859_1"),"UTF-8");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		} 
		
		return itemFind;
	}

	public static int getPageNumber(HttpServletRequest request) {
		int pageNumber = 1;
		try {			
			pageNumber = Integer.parseInt(request.getParameter("page"));
		} catch (Exception e) {}
		
		return pageNumber;
	}

	public static int getMaxEntriesPerPage(HttpServletRequest request) {
		int maxEntriesPerPage=50;
		try {
			maxEntriesPerPage = Integer.parseInt(request.getParameter("maxEntriesPerPage"));			
		}catch (Exception e) {}
		
		return maxEntriesPerPage;
	}

	public static int getLineNumber(HttpServletRequest request) {
				
		int lineNumber = 1;
		try {			
			lineNumber = Integer.parseInt(request.getParameter("lineNumber"));
		} catch (Exception e) {}
		
		return lineNumber;
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public static PageJsp getScrollableResultsPage(String hibQuery, int pageNumber, int pageSize) {
		PageJsp page = new PageJsp();
		
		ArrayList result = new ArrayList();
						
		long before = new Date().getTime();
		Session session = null;

		try {
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			session.beginTransaction();
			
			int offset = (pageNumber-1)*pageSize;
			
			Query q = session.createQuery(hibQuery);
						
			ScrollableResults scr = q.scroll();
			scr.last();
			if(scr.getRowNumber()>-1) {
				scr.first();			
				scr.scroll(offset);
				
				int i=0;
				while (pageSize>i++) {												
					result.add(scr.get(0));				
					if(!scr.next()) {
						break;
					}
				}
				scr.last();			
				page.setAllElements(scr.getRowNumber()+1);
				page.setCurrentPage(pageNumber);			
				page.setMaxEntriesPerPage(pageSize);				
				page.setResultList(result);
			}
			session.getTransaction().commit();						
			
		}catch(Exception e) {			
			System.out.println(e.toString());
		}finally {
			session.close();
		}
		
		long after = new Date().getTime();
		double resultTime = (after-before)/1000d;
		System.out.println("resultTime: "+resultTime+" second");

		return page;
	}	
	
	public static void setAttributeJsp(HttpServletRequest request, PageJsp p) {
		
		request.setAttribute("maxEntriesPerPage", p.getMaxEntriesPerPage());
		request.setAttribute("allElement", p.getAllElements());
		if(p.getAllElements()>0) {
			
			request.setAttribute("itemResults", p.getResultList() );
			request.setAttribute("offset", (p.getCurrentPage()-1)*p.getMaxEntriesPerPage());
			request.setAttribute("currentPage", p.getCurrentPage());
			
			if(p.isNextPage()==true) {
				request.setAttribute("nextPage", p.getCurrentPage()+1);
			}
			
			if(p.isPreviousPage()==true) {
				request.setAttribute("previousPage", p.getCurrentPage()-1);
			}
			
			if(p.isFirstPage()==true) {
				request.setAttribute("firstPage", 1);
			}		
			
			if(p.isLastPage()==true && p.getMaxEntriesPerPage()!=0) {
				if(p.getAllElements()%p.getMaxEntriesPerPage()==0) {
					request.setAttribute("lastPage", p.getAllElements()/p.getMaxEntriesPerPage());	
				}else {
					request.setAttribute("lastPage", p.getAllElements()/p.getMaxEntriesPerPage()+1);
				}						
			}
		}
	}	
}
